package net.zoneland.knowledge.utils;

import com.google.common.base.Function;
import com.google.common.base.Preconditions;
import com.google.common.collect.Lists;
import net.zoneland.knowledge.enums.MeetingSignInEnum;
import net.zoneland.knowledge.exceptions.WorkApplyException;
import net.zoneland.knowledge.model.WorkApplyInfoAttachment;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * excel工具类.
 *
 * @author zmq
 * @Date 2022/8/22 17:44
 */
public class ExcelUtils {
    /**
     * 日志.
     */
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
    /**
     * 序号匹配正则.
     */
    private static final Pattern SORT_NUMBER_REG = Pattern.compile("^[0-9]+(\\.[0-9]+)$");

    /**
     * 合并附件时，分隔不同人上报的内容的分隔符。
     */
    public static final String CONTENT_SPLIT_SYMBOL = "\n";

    /**
     * excel单元格式化
     */
    private static final DataFormatter dataFormatter = new DataFormatter();

    /**
     * 默认时间转换格式
     */
    private static final String TIME_FORMATTER = "yyyy/MM/dd";

    /**
     * 模板用于判断是否求和的特殊标记字段
     */
    private static final Pattern SUM_FIELD = Pattern.compile("[S][U][M][(].*[)]");

    /**
     * 构造.
     */
    private ExcelUtils() {

    }

    /**
     * excel附件合并，增加判断条件，必须有模板附件，并且设定是否合并
     * @param targetContent 模板附件
     * @param sourceContents 上传的附件内容
     * @param resultContent 最后合并结果附件，如果要合并附件，这个就不能为空。
     * @param isMerge 是否合并。是才会合并，否就不合并附件。
     */
    public static void writeToResultContent(final WorkApplyInfoAttachment targetContent,
                                            final List<WorkApplyInfoAttachment> sourceContents,
                                            final WorkApplyInfoAttachment resultContent,final boolean isMerge) {
        if (targetContent!=null && isMerge){
            writeToResultContent(targetContent,sourceContents,resultContent);
        }
    }

    /**
     * excel附件合并
     * @param targetContent 模板附件
     * @param sourceContents 上传附件list
     * @param resultContent 最后合并结果附件，不能为空，初始应该跟模板附件一样。
     */
    public static void writeToResultContent(final WorkApplyInfoAttachment targetContent,
                                            final List<WorkApplyInfoAttachment> sourceContents,
                                            final WorkApplyInfoAttachment resultContent) {
        Preconditions.checkNotNull(targetContent, "目标文件附件为空！");
        Preconditions.checkNotNull(resultContent, "用于合并的附件为空！");
        Preconditions.checkNotNull(CollectionUtils.isNotEmpty(sourceContents), "源文件附件为空！");
        Preconditions.checkArgument(ArrayUtils.isNotEmpty(targetContent.getAttachment()), "目标文件附件内容为空！");
        Preconditions.checkArgument(ArrayUtils.isNotEmpty(resultContent.getAttachment()), "用于合并的附件内容为空！");

        final Workbook workBook = getWorkBook(targetContent);
        final Iterator<Sheet> sheets = workBook.sheetIterator();
        while (sheets.hasNext()){
            final Sheet sheet = sheets.next();
            final List<List<String>> targetWork = getWorkbookBySheet(sheet, ExcelUtils::getCellValue);
            final int sheetIndex = workBook.getSheetIndex(sheet);
            sourceContents.forEach(sourceContent-> {
                Preconditions.checkArgument(sourceContent.getAttachment() != null
                                && sourceContent.getAttachment().length > 0,
                        "源文件附件内容为空！文件id：" + sourceContent.getId());
                final Workbook sourceBook = getWorkBook(sourceContent);
                final List<List<String>> workbookData = getWorkbookBySheet(sourceBook.getSheetAt(sheetIndex), ExcelUtils::getCellValueForUpload);
                Preconditions.checkArgument(CollectionUtils.isNotEmpty(workbookData),
                        "待合并的源文件数据为空！文件id：" + sourceContent.getId());
                if (LOGGER.isDebugEnabled()) {
                    LOGGER.debug("writeToResultContent targetContent id:{}, sourceContents id:{}",
                            targetContent.getSubTaskId(), sourceContent.getSubTaskId());
                }
                final byte[] newRes = writeResult(targetWork, workbookData, resultContent, sourceContent.getUploaderName(), sheetIndex);
                if (ArrayUtils.isNotEmpty(newRes)) {
                    resultContent.setAttachment(newRes);
                }
            });
        }
    }

    /**
     * 获取excel的Workbook对象。已经被close后，可以读，但不能写。
     * @param attachment
     * @return
     */
    private static Workbook getWorkBook(final WorkApplyInfoAttachment attachment){
        final String fileType = attachment.getFileName().substring(attachment.getFileName().lastIndexOf("."));
        try(final Workbook wb = initWorkbook(new ByteArrayInputStream(attachment.getAttachment()), fileType);){
            return wb;
        }catch (Exception e) {
            throw new WorkApplyException("获取文件的excel对象失败！attachmentId:" + attachment.getId(), e);
        }
    }

    /**
     * 读取单元格内容，对于时间日期，自定义日期等格式，默认转化为yyyy/MM/dd格式
     * @param cell
     * @return
     */
    public static String getCellValue(final Cell cell) {
        if (cell == null) {
            return "";
        }
        final String value = dataFormatter.formatCellValue(cell);

        if(CellType.NUMERIC == cell.getCellTypeEnum()){
            if (value.startsWith("reserved") || HSSFDateUtil.isCellDateFormatted(cell)){
                Date javaDate = cell.getDateCellValue();
                return DateHelper.getDateTime(TIME_FORMATTER, javaDate);
            }
        }
        return value;
    }

    /**
     * 对上传附件进行；读取单元格内容，
     * 如果是公式希望获取公式的值。不是公式，对于时间日期，自定义日期等格式，
     * 默认转化为yyyy/MM/dd格式
     * @param cell 单元格
     * @return
     */
    public static String getCellValueForUpload(final Cell cell) {
        if (cell == null) {
            return "";
        }
        if (CellType.FORMULA == cell.getCellTypeEnum()) {
            try {
                final String numeric = String.valueOf(cell.getNumericCellValue());
                //getNumericCellValue是double类型，转成String会变成0.0，所以根据小数点分割，看后面的值是否为0，
                // 是就返回小数点前面的内容，否则返回全部。
                final String[] split = StringUtils.split(numeric, ".");
                return StringUtils.equals("0", split[1]) ? split[0] : numeric;
            } catch (IllegalStateException e) {
                //获取excel里的公式类型的单元格的值，根据业务需求是数字类型，但也有可能不是。
                //如果不是，这里会报错，然后按照字符串类型返回。
                //异常只是用来判断，不需要额外抛出异常，或者保留异常日志。
                return getCellValue(cell);
            }
        }
        return getCellValue(cell);
    }

    /**
     * 内容写入结果附件表
     * @param targetWork 模板附件内容
     * @param workbookData 上传附件内容
     * @param resultContent 合并结果附件对象
     * @param name 上传附件的人员名称
     * @return
     */
    private static byte[] writeResult(final List<List<String>> targetWork, final List<List<String>> workbookData,
                                      final WorkApplyInfoAttachment resultContent, final String name,final int sheetIndex) {
        final String fileType = resultContent.getFileName().substring(resultContent.getFileName().lastIndexOf("."));
        try(final Workbook wbResult = initWorkbook(new ByteArrayInputStream(resultContent.getAttachment()), fileType);){
            final int sheets = wbResult.getNumberOfSheets();
            final Sheet sheetResult = sheets > sheetIndex ? wbResult.getSheetAt(sheetIndex) : wbResult.createSheet();

            // 遍历模板附件数组，第一个是行数，第二个是每一列的每个单元格
            todoRows(targetWork, sheetResult, workbookData, name, wbResult);
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            wbResult.write(baos);
            return baos.toByteArray();
        } catch (Exception e) {
            throw new WorkApplyException("写入数据到excel文件失败！attachmentId:" + resultContent.getId(), e);
        }
    }

    /**
     * 处理附件每一行内容
     * @param targetWork 模板附件内容
     * @param sheetResult
     * @param workbookData 上传附件内容
     * @param name
     */
    private static void todoRows(final List<List<String>> targetWork, final Sheet sheetResult,
                                 final List<List<String>> workbookData, final String name, final Workbook wbResult){
        final ListIterator<List<String>> targetList = targetWork.listIterator();
        final ListIterator<List<String>> workDataList = workbookData.listIterator();
        while (targetList.hasNext() || workDataList.hasNext()) {
            final int num = targetList.hasNext() ? targetList.nextIndex() : workDataList.nextIndex();
            final Row row = Optional.ofNullable(sheetResult.getRow(num)).orElseGet(() ->sheetResult.createRow(num));
            todoCell(targetList.hasNext() ? targetList.next() : Collections.emptyList(),
                    workDataList.hasNext() ? workDataList.next() : Collections.emptyList(),
                    row, name, wbResult);
        }

    }

    /**
     * 处理每一个单元格数据
     * @param targetList 附件行的内容
     * @param workList 上传的内容
     * @param row 合并的结果行
     * @param name
     */
    private static void todoCell(final List<String> targetList, final List<String> workList, final Row row,
                                 final String name, final Workbook wbResult) {
        final ListIterator<String> targetIterator = targetList.listIterator();
        final ListIterator<String> workIterator = workList.listIterator();
        while (targetIterator.hasNext() || workIterator.hasNext()) {
            final int num = targetIterator.hasNext() ? targetIterator.nextIndex() : workIterator.nextIndex();
            final String targetCell = targetIterator.hasNext() ? targetIterator.next() : "";
            final String workCell = workIterator.hasNext() ? workIterator.next() : "";
            if (!targetCell.equals(workCell)) {
                if (SUM_FIELD.matcher(targetCell).find()) {
                    setCellForSum(workCell, row, num, wbResult, targetCell);
                } else {
                    setCell(workCell, name, row, num, wbResult);
                }
            } else if (SUM_FIELD.matcher(targetCell).find()) {
                //如果模板是需要求和，并且结果附件和模板一致，需要重新setCellFormula，否则会可能自动计算结果并给出结果值，而不是实际的SUM
                final String cellValue = getCellValue(row, num);
                if (StringUtils.equals(cellValue, targetCell)) {
                    row.getCell(num).setCellFormula(targetCell);
                }
            }
        }
    }

    /**
     * 附件单元格求和
     * @param dataCell 上传附件单元格内容
     * @param row 合并附件的行
     * @param num 合并附件行对应的单元格的列数
     * @param wbResult 结果附件
     * @param targetCell 模板文字
     */
    private static void setCellForSum(final String dataCell, final Row row, final int num,
                                      final Workbook wbResult, final String targetCell){

        final BigDecimal data = StringUtils.isNotBlank(dataCell) ? new BigDecimal(dataCell) : BigDecimal.ZERO;

        //上传附件内容需要加上上传人的名称，用来展现

        final Cell resultCell = Optional.ofNullable(row.getCell(num)).orElseGet(() -> row.createCell(num));
        final String cellValue = StringUtils.remove(getCellValue(resultCell), targetCell);

        final BigDecimal resultData = StringUtils.isNotBlank(cellValue) ? new BigDecimal(cellValue).add(data) : data;

        final CellStyle cellStyle = getCellStyle(resultCell.getCellStyle(), wbResult);
        final Cell cell = row.createCell(num);
        cell.setCellValue(resultData.toString());
        cell.setCellStyle(cellStyle);
    }

    /**
     * 设置单元格类型
     * @param resultCellStyle
     * @param wbResult
     * @return
     */
    private static CellStyle getCellStyle(final CellStyle resultCellStyle, final Workbook wbResult){
        final short fontIndex = resultCellStyle.getFontIndex();
        final Font font = wbResult.getFontAt(fontIndex);
        final CellStyle cellStyle = wbResult.createCellStyle();
        cellStyle.setFont(font);
        //开启自动换行
        cellStyle.setWrapText(true);
        //文字格式
        cellStyle.setAlignment(resultCellStyle.getAlignmentEnum());
        return cellStyle;
    }

    /**
     * 合并赋值
     * @param dataCell 上传附件单元格内容
     * @param name 上传人名称
     * @param row 合并附件的行
     * @param num 合并附件行对应的单元格的列数
     */
    private static void setCell(final String dataCell, final String name, final Row row, final int num, final Workbook wbResult){
        //如果选择清除处理人选项，上传附件内容不需要加上上传人的名称，否则需要加上上传人的名称，用来展现
        final String targetValue = StringUtils.isBlank(name) ? dataCell : String.format("%s(%s)", dataCell, name);

        final Cell resultCell = Optional.ofNullable(row.getCell(num)).orElseGet(() -> row.createCell(num));
        final String cellValue = getCellValue(resultCell);

        final CellStyle cellStyle = getCellStyle(resultCell.getCellStyle(), wbResult);
        final Cell cell = row.createCell(num);
        cell.setCellValue(StringUtils.isNotBlank(cellValue) ?
                cellValue + CONTENT_SPLIT_SYMBOL + targetValue:targetValue);
        cell.setCellStyle(cellStyle);
    }

    /**
     * 根据sheet页获取这一页的数据，用两层list组合，第一个是行，第二个是行里单元格内容。
     * @param sheet
     * @param function 获取单元格内容的方式
     * @return
     */
    public static List<List<String>> getWorkbookBySheet(final Sheet sheet, final Function<Cell, String> function) {
        return IntStream.range(0, sheet.getLastRowNum()+1).mapToObj(rowNum -> {
            return Optional.ofNullable(sheet.getRow(rowNum)).map(row -> {
                return IntStream.range(0, row.getLastCellNum()).mapToObj(index -> {
                    return Optional.ofNullable(row.getCell(index)).map(cell -> {
                        return function.apply(cell);
                    }).orElse("");
                }).collect(Collectors.toList());
            }).orElse(Collections.emptyList());
        }).collect(Collectors.toList());
    }

    /**
     * 合并excel.
     *
     * @param targetContent  待合并的目标文件
     * @param sourceContents 待合并的源文件集合
     */
    public static void writeContentToFile(final WorkApplyInfoAttachment targetContent,
                                          final List<WorkApplyInfoAttachment> sourceContents) {
        Preconditions.checkNotNull(targetContent, "目标文件附件为空！");
        Preconditions.checkNotNull(CollectionUtils.isNotEmpty(sourceContents), "源文件附件为空！");
        Preconditions.checkArgument(targetContent.getAttachment() != null
                && targetContent.getAttachment().length > 0, "目标文件附件内容为空！");
        List<List<String>> needMergeData = Lists.newArrayList();
        List<List<String>> workbookData = null;
        byte[] newRes = null;
        for (final WorkApplyInfoAttachment sourceContent : sourceContents) {
            Preconditions.checkArgument(sourceContent.getAttachment() != null
                            && sourceContent.getAttachment().length > 0,
                    "源文件附件内容为空！文件id：" + sourceContent.getId());
            workbookData = getWorkbookData(sourceContent);
            Preconditions.checkArgument(CollectionUtils.isNotEmpty(workbookData),
                    "待合并的源文件数据为空！文件id：" + sourceContent.getId());
            CollectionUtils.addAll(needMergeData, workbookData.iterator());
            newRes = writeContentToTargetSheet(targetContent, sourceContent);
        }
        //写入数据到目标工作表
//     byte[] newRes = writeDataToTargetSheet(targetContent, needMergeData);
        LOGGER.info("写入数据到目标工作表:{}", newRes);
        targetContent.setAttachment(newRes);
    }

    /**
     * 获取工作表的数据.
     *
     * @param attachment 附件
     * @return 工作表数据
     */
    public static byte[] writeContentToTargetSheet(final WorkApplyInfoAttachment targetAttachment,
                                                   final WorkApplyInfoAttachment sourceAttachment) {
        InputStream isTarget = null;
        Workbook wbTarget = null;
        InputStream isSource = null;
        Workbook wbSource = null;
        try {
            isTarget = new ByteArrayInputStream(targetAttachment.getAttachment());
            String fileType = targetAttachment.getFileName().substring(targetAttachment.getFileName().lastIndexOf("."));
            wbTarget = initWorkbook(isTarget, fileType);
            Sheet sheetTarget = wbTarget.getSheetAt(0);
            isSource = new ByteArrayInputStream(sourceAttachment.getAttachment());
            // String fileType = attachment.getFileName().substring(attachment.getFileName().lastIndexOf("."));
            wbSource = initWorkbook(isSource, fileType);
            Sheet sheetSource = wbSource.getSheetAt(0);
            // 获取sheet中，有数据的行数
            int rowNum = sheetSource.getPhysicalNumberOfRows();
            // List<List<String>> physicalDatas = Lists.newArrayList();
            for (int i = 0; i < rowNum; i++) {
                // 获取当前行
                Row targetRow = sheetTarget.getRow(i);
                Row sourceRow = sheetSource.getRow(i);
                String firstCellVal = getCellValue(targetRow, 0);
                if (StringUtils.isBlank(firstCellVal) || !SORT_NUMBER_REG.matcher(firstCellVal).find()) {

                } else {
                    Cell cell = null;
                    for (int j = 0; j < targetRow.getPhysicalNumberOfCells(); j++) {
                        if (targetRow.getCell(j).toString().isEmpty()
                                && !targetRow.getCell(j).toString().equals(sourceRow.getCell(j).toString())) {
                            LOGGER.info("目标数据:{}", targetRow.getCell(j));
                            LOGGER.info("待写入数据:{}", sourceRow.getCell(j));
                            cell = targetRow.getCell(j);
                            cell.setCellValue(sourceRow.getCell(j).toString());
                        }

                    }
                }
            }
            // return physicalDatas;
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            wbTarget.write(baos);
            return baos.toByteArray();
        } catch (Exception e) {
            throw new WorkApplyException("写入数据到excel文件失败！attachmentId:" + targetAttachment.getId(), e);
        } finally {
            closeInputStreamAndWebBook(isTarget, wbTarget);
            closeInputStreamAndWebBook(isSource, wbSource);
        }
    }

    /**
     * 获取工作表的数据.
     *
     * @param attachment 附件
     * @return 工作表数据
     */
    public static List<List<String>> getWorkbookData(final WorkApplyInfoAttachment attachment) {
        final String fileType = attachment.getFileName().substring(attachment.getFileName().lastIndexOf("."));
        try(final Workbook wb = initWorkbook(new ByteArrayInputStream(attachment.getAttachment()), fileType);){
            Sheet sheet = wb.getSheetAt(0);
            //获取sheet中，有数据的行数
            int rowNum = sheet.getPhysicalNumberOfRows();
            List<List<String>> physicalDatas = Lists.newArrayList();
            for (int i = 0; i < rowNum; i++) {
                //获取当前行
                Row row = sheet.getRow(i);
                List<String> rowData = getRowData(row);
                if (CollectionUtils.isNotEmpty(rowData)) {
                    physicalDatas.add(rowData);
                }
            }
            return physicalDatas;
        } catch (Exception e) {
            throw new WorkApplyException("获取文件的excel对象失败！attachmentId:" + attachment.getId(), e);
        }
    }

    /**
     * 关闭文件流和工作表.
     *
     * @param is 文件流
     * @param wb 工作表
     */
    private static void closeInputStreamAndWebBook(final InputStream is, final Workbook wb) {
        try {
            if (wb != null) {
                wb.close();
            }
            if (is != null) {
                is.close();
            }
        } catch (IOException e) {
            LOGGER.error("关闭文件流失败！", e);
        }
    }

    /**
     * 获取excel对象.
     *
     * @param is       输入流
     * @param fileType 文件类型
     * @return excel工作表
     * @throws IOException
     */
    public static Workbook initWorkbook(final InputStream is, final String fileType) throws IOException {
        Workbook wb = null;
        if (".xls".equals(fileType)) {
            wb = new HSSFWorkbook(is);
        } else if (".xlsx".equals(fileType)) {
            wb = new XSSFWorkbook(is);
        } else {
            throw new WorkApplyException("文件格式错误，fileType:" + fileType);
        }
        return wb;
    }

    /**
     * 获取行的数据.
     *
     * @param row 行对象
     * @return 数据集合
     */
    private static List<String> getRowData(final Row row) {
        List<String> rowDatas = Lists.newArrayList();
        if (row == null) {
            return rowDatas;
        }
        //开始对cell进行处理。
        String firstCellVal = getCellValue(row, 0);
        if (StringUtils.isBlank(firstCellVal) || !SORT_NUMBER_REG.matcher(firstCellVal).find()) {
            return rowDatas;
        }
        int cellNum = row.getPhysicalNumberOfCells();
        return IntStream.range(0, cellNum).mapToObj(index -> getCellValue(row, index)).collect(Collectors.toList());
    }

    /**
     * 获取行的第index个单元格的数据.
     *
     * @param row   行对象
     * @param index 列单元序号
     * @return 值
     */
    private static String getCellValue(final Row row, final int index) {
        try {
            if (row == null) {
                return "";
            }
            Cell cell = row.getCell(index);
            if (cell == null) {
                return "";
            }
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:
                    return String.valueOf(cell.getNumericCellValue());
                case BLANK:
                case _NONE:
                    return "";
                case ERROR:
                    return String.valueOf(cell.getErrorCellValue());
                case STRING:
                    return String.valueOf(cell.getStringCellValue());
                case BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                case FORMULA:
                    return String.valueOf(cell.getCellFormula());
                default:
                    return cell.getStringCellValue();
            }
        } catch (Exception e) {
            LOGGER.error("获取单元格值失败，index:{}", index, e);
        }
        return "";
    }

    /**
     * 校验模板和上传附件是否一致
     * @param targetContent 模板
     * @param sourceContents 上传附件
     * @return 错误信息
     */
    public static String checkAttachment(final WorkApplyInfoAttachment targetContent,
                                         final List<WorkApplyInfoAttachment> sourceContents) {
        Preconditions.checkNotNull(targetContent, "目标文件附件为空！");
        Preconditions.checkNotNull(CollectionUtils.isNotEmpty(sourceContents), "源文件附件为空！");
        final int sheetNum = Optional.ofNullable(getWorkBook(targetContent)).map(Workbook::getNumberOfSheets).orElse(0);
        if (sheetNum <= 0) {
            return "模板没有sheet页。";
        }
        final String errorMsg = sourceContents.stream().filter(source -> {
            final int sourceNum = Optional.ofNullable(getWorkBook(source))
                    .map(Workbook::getNumberOfSheets)
                    .orElse(0);
            return sheetNum != sourceNum;
        }).map(WorkApplyInfoAttachment::getFileName)
                .collect(Collectors.joining(","));

        if (StringUtils.isNotBlank(errorMsg)){
            return String.format("%s，sheet页与模板不一致。请确认上传附件是否正确。", errorMsg);
        }
        return null;
    }

    /**
     * excel文件导入
     * @param is
     * @return
     * @throws IOException
     */
    public static List<Map<String, String>> meetingExcel(final InputStream is) throws IOException {
        try (XSSFWorkbook workbook = new XSSFWorkbook(is)) {
            final Sheet sheet = workbook.getSheetAt(0);
            final int lastRowNum = Optional.ofNullable(sheet).map(Sheet::getLastRowNum).orElse(0);
            if (lastRowNum <= 0) {
                return Collections.emptyList();
            }
            final Row firstRow = sheet.getRow(0);
            final HashMap<String, Integer> titleMap = new HashMap<>();
            IntStream.range(firstRow.getFirstCellNum(), firstRow.getLastCellNum())
                    .forEach(i -> {
                        final Cell cell = firstRow.getCell(i);
                        final String fieldByTitle = MeetingSignInEnum.getFieldByTitle(cell.getStringCellValue().trim());
                        titleMap.put(fieldByTitle, i);
                    });

            return getContent(sheet, lastRowNum, titleMap);
        }
    }


    /**
     * 获取文件内容
     * @param sheet
     * @param lastRowNum
     * @param titleMap
     * @return
     */
    private static List<Map<String, String>> getContent(final Sheet sheet, final int lastRowNum, final Map<String, Integer> titleMap) {
        final DecimalFormat fm = new DecimalFormat("0");
        return IntStream.rangeClosed(1, lastRowNum)
                .mapToObj(i -> sheet.getRow(i))
                .filter(Objects::nonNull)
                .map(row -> titleMap.entrySet().stream()
                        .collect(Collectors.toMap(
                                Map.Entry::getKey,
                                entry -> Optional.ofNullable(row.getCell(entry.getValue()))
                                        .map(cell -> {
                                            switch (cell.getCellTypeEnum()) {
                                                case NUMERIC:
                                                    return fm.format(cell.getNumericCellValue());
                                                case STRING:
                                                    return cell.getStringCellValue().trim();
                                                default:
                                                    return " ";
                                            }
                                        }).orElse(null)
                        ))
                ).collect(Collectors.toList());
    }
}
